﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace VPLink2013.Batch
{
    public static class SQLExecution
    {
        public const string connectionString = @"server=SD-MOSS-SQL\MOSS;database=VPLinkLog;Integrated Security=True;Connect Timeout=30;MultipleActiveResultSets=True;";

        private static string GetValueReader(SqlDataReader Reader, int position)
        {
            string strValue = string.Empty;
            if (!Reader.IsDBNull(position))
            {
                strValue = Reader.GetValue(position).ToString();
            }
            return strValue;
        }

        public static string GetDirectionId(string directionName)
        {
            string id = string.Empty;
            string sqlReq = string.Format(@"SELECT id FROM Directions 
											 WHERE nom = '{0}'", directionName.Replace("'", "''"));
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdId = new SqlCommand(sqlReq, conn);
            SqlDataReader idReader = cmdId.ExecuteReader();
            if (idReader.Read())
            {
                id = GetValueReader(idReader, 0);
            }
            conn.Close();
            return id;
        }

        public static string GetDepartementId(string departementName, string directionId)
        {
            string id = string.Empty;
            string sqlReq = string.Format(@"SELECT id FROM Departements 
											 WHERE nom = '{0}' 
											 and direction = {1}",
                                             departementName.Replace("'", "''"), directionId);
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdId = new SqlCommand(sqlReq, conn);
            SqlDataReader idReader = cmdId.ExecuteReader();
            if (idReader.Read())
            {
                id = GetValueReader(idReader, 0);
            }
            conn.Close();
            return id;
        }

        public static string GetServiceId(string serviceName, string departementId)
        {
            string id = string.Empty;
            string sqlReq = string.Format(@"SELECT id FROM Services 
											 WHERE nom = '{0}' 
											 and departement = {1}",
                                             serviceName.Replace("'", "''"), departementId);
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdId = new SqlCommand(sqlReq, conn);
            SqlDataReader idReader = cmdId.ExecuteReader();
            if (idReader.Read())
            {
                id = GetValueReader(idReader, 0);
            }
            conn.Close();
            return id;
        }


        public static string GetDepartementIdByName(string departementName)
        {
            string departementId = string.Empty;
            string sqlReq = string.Format(@"SELECT id FROM Departements 
											 WHERE nom = '{0}'",
                                 departementName.Replace("'", "''"));
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdId = new SqlCommand(sqlReq, conn);
            SqlDataReader idReader = cmdId.ExecuteReader();
            if (idReader.Read())
            {
                departementId = GetValueReader(idReader, 0);
            }
            conn.Close();
            return departementId;
        }
        public static string GetServiceIdByName(string serviceName)
        {
            string serviceId = string.Empty;
            string sqlReq = string.Format(@"SELECT id FROM Services 
											 WHERE nom = '{0}'",
                                 serviceName.Replace("'", "''"));
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdId = new SqlCommand(sqlReq, conn);
            SqlDataReader idReader = cmdId.ExecuteReader();
            if (idReader.Read())
            {
                serviceId = GetValueReader(idReader, 0);
            }
            conn.Close();
            return serviceId;
        }
        public static bool IsOrphanService(string serviceName)
        {
            bool isOrphan = true;
            string sqlReq = string.Format(@"SELECT departement FROM Services 
											 WHERE nom = '{0}'",
                                             serviceName.Replace("'", "''"));
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdId = new SqlCommand(sqlReq, conn);
            SqlDataReader idReader = cmdId.ExecuteReader();
            if (idReader.Read())
            {
                if (!string.IsNullOrEmpty(GetValueReader(idReader, 0)))
                {
                    isOrphan = false;
                }
            }
            conn.Close();
            return isOrphan;
        }

        public static bool IsOrphanDepartement(string departementName)
        {
            bool isOrphan = true;
            string sqlReq = string.Format(@"SELECT direction FROM Departements 
											 WHERE nom = '{0}'",
                                             departementName.Replace("'", "''"));
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdId = new SqlCommand(sqlReq, conn);
            SqlDataReader idReader = cmdId.ExecuteReader();
            if (idReader.Read())
            {
                if (!string.IsNullOrEmpty(GetValueReader(idReader, 0)))
                {
                    isOrphan = false;
                }
            }
            conn.Close();
            return isOrphan;
        }

        #region Insert Into
        public static string InsertDirection(string directionName)
        {
            string directionId = string.Empty;
            string sqlInsert = string.Format(@"INSERT INTO Directions([nom])
												VALUES('{0}')", directionName.Replace("'", "''"));

            string sqlLastId = string.Format(@"SELECT TOP 1 id FROM Directions order by id desc");

            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdInsert = new SqlCommand(sqlInsert, conn);
            cmdInsert.ExecuteNonQuery();
            SqlCommand cmdGetLastId = new SqlCommand(sqlLastId, conn);
            directionId = cmdGetLastId.ExecuteScalar().ToString();
            conn.Close();
            return directionId;
        }

        public static string InsertDepartement(string departementName, string directionId)
        {
            string departementId = string.Empty;
            string sqlInsert = string.Format(@"INSERT INTO Departements([nom],[direction])
												VALUES('{0}',{1})", departementName.Replace("'", "''"), directionId);

            string sqlLastId = string.Format(@"SELECT TOP 1 id FROM departements order by id desc");

            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdInsert = new SqlCommand(sqlInsert, conn);
            cmdInsert.ExecuteNonQuery();
            SqlCommand cmdGetLastId = new SqlCommand(sqlLastId, conn);
            departementId = cmdGetLastId.ExecuteScalar().ToString();
            conn.Close();
            return departementId;
        }

        public static void InsertService(string servicetName, string departementId)
        {
            string sqlInsert = string.Format(@"INSERT INTO Services ([nom],[departement])
												VALUES('{0}',{1})", servicetName.Replace("'", "''"), departementId);
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdInsert = new SqlCommand(sqlInsert, conn);
            cmdInsert.ExecuteNonQuery();
            conn.Close();
        }
        #endregion

        #region Update relationships
        public static void UpdateDepartement(string departementId, string directionId)
        {
            string sqlUpdate = string.Format(@"UPDATE Departements SET [direction] = {0} WHERE id = {1}", directionId, departementId);
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, conn);
            cmdUpdate.ExecuteNonQuery();
            conn.Close();
        }
        public static void UpdateService(string serviceId, string departementId)
        {
            string sqlUpdate = string.Format(@"UPDATE Services SET [departement] = {0} WHERE id = {1}", departementId, serviceId);
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, conn);
            cmdUpdate.ExecuteNonQuery();
            conn.Close();
        }
        #endregion
    }
}
